
# we imort our librairies
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.patches import Patch
import seaborn as sns

import requests
import os
import re
from bs4 import BeautifulSoup
import datetime
import locale

from sklearn.model_selection import train_test_split
from sklearn import linear_model, preprocessing, cluster
from sklearn.metrics import r2_score, mean_squared_error

from statsmodels.tsa.holtwinters import SimpleExpSmoothing
from statsmodels.tsa.seasonal import seasonal_decompose
from statsmodels.tsa.statespace.sarimax import SARIMAX

import plotly.express as px
from ipyleaflet import Map, Polyline, LegendControl


# we request the url
req = requests.get('https://www.vendeeglobe.org/fr/classement')

# we raise an Exception if the status code is not 200
if req.status_code != 200:
    raise Exception('Couldn\'t get open URL')

# we get our soup from the downloaded content
soup = BeautifulSoup(req.content)

dates = []
# we extract the arrival dates from the website
for td in soup.find('table', attrs={'class': 'ranking-report'}).findAll('td', attrs={'class': 'row-layout', 'colspan': '2'}):
    dates.append(datetime.datetime.strptime(td.text, 'Date arrivée : %d/%m/%Y %H:%M:%S'))

# we extract the first arrival
first = min(dates)
# we extract the available values from the website's list
for option in soup.find('select', attrs={'class': 'form__input m--select onsubmit-rank'}).findAll('option'):
    date = option.attrs['value']
    # we skip if the date is empty or after the first arrival
    if date != '' and datetime.datetime.strptime(date, '%Y%m%d_%H%M%S') < first:
        file = requests.get('https://www.vendeeglobe.org/download-race-data/vendeeglobe_'+date+'.xlsx')
        #open('data/'+date+'.xlsx', 'wb').write(file.content)


# we get the list of the files we downloaded
# we remove the .files, which are temporary files created by Excel when we open a file,
# we also exclude the first and the last file
files = [f for f in os.listdir('data/') if not f.startswith('.')][1:-1]

# we load a file
def load_data(filename):
    df = pd.read_excel('data/'+filename, usecols='B:U', skiprows=[0,1,2,3],
                    names=['Rank', 'Nat. Sail', 'Skipper/crew', 'Hour FR', 'Latitude', 'Longitude',
                    'Heading ° 30min', 'Speed kts 30min', 'VMG kts 30min', 'Distance nm 30min',
                    'Heading ° last report', 'Speed kts last report', 'VMG kts last report','Distance nm last report',
                    'Heading ° 24h', 'Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF', 'DTL'])

    # we drop the na values
    df = df.dropna()

    # we split the string values into separate columns
    df['Nationality'] = df['Nat. Sail'].str.split(' ').str[0].str.replace('\n', '')
    df['Sail'] = df['Nat. Sail'].str.split(' ').str[1].apply(int)
    df = df.drop('Nat. Sail', axis=1)

    df['Skipper'] = df['Skipper/crew'].str.split('\n').str[0]
    df['Crew'] = df['Skipper/crew'].str.split('\n').str[1].str.title()
    df = df.drop('Skipper/crew', axis=1)
    
    # we convert the latitude and longitude into decimal
    # Decimal degrees = Degrees + (Minutes/60) + (Seconds/3600), N & E = + /  S & W = - 
    df['Latitude'] = df['Latitude'].apply(lambda s: (1 if s.split('°')[1].split('.')[1][-1] == 'N' else -1)
                                                    * (int(s.split('°')[0])
                                                    + int(s.split('°')[1].split('.')[0])/60
                                                    + int(re.sub('[^0-9]', '', s.split('°')[1].split('.')[1]))/3600))
    df['Longitude'] = df['Longitude'].apply(lambda s: (1 if s.split('°')[1].split('.')[1][-1] == 'E' else -1)
                                                    * (int(s.split('°')[0])
                                                    + int(s.split('°')[1].split('.')[0])/60
                                                    + int(re.sub('[^0-9]', '', s.split('°')[1].split('.')[1]))/3600))

    # we convert the time into a proper datetime value
    date = re.search('([0-9]{8})', filename).group(0)
    df['Hour FR'] = df['Hour FR'].apply(lambda x: datetime.datetime.strptime(date+'_'+x[:5], '%Y%m%d_%H:%M'))

    # we set it as the index
    df = df.set_index('Hour FR')

    # we convert all the numerical values into floats
    df['Rank'] = df['Rank'].apply(int)
    numerical_cols = ['Heading ° 30min', 'Speed kts 30min', 'VMG kts 30min', 'Distance nm 30min',
                    'Heading ° last report', 'Speed kts last report', 'VMG kts last report','Distance nm last report',
                    'Heading ° 24h', 'Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF', 'DTL']
    df[numerical_cols] = df[numerical_cols].apply(lambda x: x.str.replace(re.compile('[a-z]+|°'), '')).apply(pd.to_numeric)

    # we reorder the columns
    df = df[['Rank', 'Nationality', 'Sail', 'Skipper', 'Crew', 'Latitude', 'Longitude', *numerical_cols]]

    return df

# For example let's read the first file
df = load_data(files[1])
df.head()


df_list = []
for f in files:
    df_list.append(load_data(f))
df_all_rankings = pd.concat(df_list)
df_all_rankings.head()


# we save the dataframe
df_all_rankings.to_pickle('df_all_rankings.pkl')


# we import it
df_all_rankings = pd.read_pickle('df_all_rankings.pkl')


df_all_rankings.shape

(13703, 21)


df_all_rankings.groupby(['Sail', 'Nationality', 'Skipper', 'Crew'])['Distance nm 24h'].mean().sort_values(ascending=False)

Sail  Nationality  Skipper               Crew                                  
79    FRA          Charlie Dalin         Apivia                                    331.893971
59    FRA          Thomas Ruyant         Linkedout                                 330.590249
18    FRA          Louis Burton          Bureau Vallée 2                           330.461411
10    MON          Boris Herrmann        Seaexplorer - Yacht Club De Monaco        328.065353
85    FRA          Kevin Escoffier       Prb                                       327.411679
17    FRA          Yannick Bestaven      Maître Coq Iv                             327.220332
34    ITA          Giancarlo Pedote      Prysmian Group                            324.185062
9     FRA          Benjamin Dutreux      Omia - Water Family                       320.427386
1000  FRA          Damien Seguin         Groupe Apicil                             320.201660
1     FRA          Jean Le Cam           Yes We Cam !                              319.442324
27    FRA          Isabelle Joschke      Macsf                                     318.225266
2     FRFRA        Armel Tripon          L'Occitane En Provence                    316.875934
4     FRA          Sébastien Simon       Arkea Paprec                              314.924684
53    FRA          Maxime Sorel          V And B Mayenne                           312.527593
8     FRA          Jérémie Beyou         Charal                                    307.926349
109   FRA          Samantha Davies       Initiatives - Coeur                       306.887117
99    GBR          Alex Thomson          Hugo Boss                                 304.442949
30    FRA          Clarisse Cremer       Banque Populaire X                        304.438797
49    FRA          Romain Attanasio      Pure - Best Western Hotels And Resorts    292.501660
14    FRA          Arnaud Boissieres     La Mie Câline - Artisans Artipôle         285.906681
11    JPN          Kojiro Shiraishi      Dmg Mori Global One                       284.768880
92    FRA          Stéphane Le Diraison  Time For Oceans                           284.612241
7     SUI          Alan Roura            La Fabrique                               282.281120
33    ESP          Didac Costa           One Planet One Ocean                      279.685270
777   GBR          Pip Hare              Medallia                                  276.386722
71    FRA          Manuel Cousin         Groupe Sétin                              273.325934
6     FRA          Nicolas Troussel      Corum L'Épargne                           269.396000
50    FRA          Miranda Merron        Campagne De France                        264.960373
83    FRA          Clément Giraud        Compagnie Du Lit - Jiliti                 264.628008
222   FIN          Ari Huusela           Stark                                     248.431535
72    FRA          Alexia Barrier        Tse - 4Myplanet                           242.834232
56    FRA          Fabrice Amedeo        Newrest - Art Et Fenetres                 236.703500
69    FRFRA        Sébastien Destremau   Merci                                     212.909353
Name: Distance nm 24h, dtype: float64


df_all_rankings.groupby(['Nationality', 'Skipper', 'Crew'])['Rank'].mean().sort_values()

Nationality  Skipper               Crew                                  
FRA          Charlie Dalin         Apivia                                     2.286902
             Thomas Ruyant         Linkedout                                  3.520747
             Yannick Bestaven      Maître Coq Iv                              4.373444
             Jean Le Cam           Yes We Cam !                               5.599585
             Kevin Escoffier       Prb                                        5.846715
             Damien Seguin         Groupe Apicil                              6.383817
             Louis Burton          Bureau Vallée 2                            6.819502
MON          Boris Herrmann        Seaexplorer - Yacht Club De Monaco         6.939834
FRA          Nicolas Troussel      Corum L'Épargne                            7.260000
GBR          Alex Thomson          Hugo Boss                                  7.461538
FRA          Benjamin Dutreux      Omia - Water Family                        7.545643
             Samantha Davies       Initiatives - Coeur                       10.337423
             Sébastien Simon       Arkea Paprec                              10.398734
ITA          Giancarlo Pedote      Prysmian Group                            10.454357
FRA          Maxime Sorel          V And B Mayenne                           10.678423
             Isabelle Joschke      Macsf                                     11.510638
             Romain Attanasio      Pure - Best Western Hotels And Resorts    13.402490
             Clarisse Cremer       Banque Populaire X                        13.636929
SUI          Alan Roura            La Fabrique                               16.340249
FRFRA        Armel Tripon          L'Occitane En Provence                    16.350622
FRA          Arnaud Boissieres     La Mie Câline - Artisans Artipôle         17.885177
             Stéphane Le Diraison  Time For Oceans                           17.894191
GBR          Pip Hare              Medallia                                  19.226141
ESP          Didac Costa           One Planet One Ocean                      19.993776
FRA          Manuel Cousin         Groupe Sétin                              21.029046
             Jérémie Beyou         Charal                                    22.369295
JPN          Kojiro Shiraishi      Dmg Mori Global One                       23.136929
FRA          Miranda Merron        Campagne De France                        23.852697
             Clément Giraud        Compagnie Du Lit - Jiliti                 24.946058
             Alexia Barrier        Tse - 4Myplanet                           25.139004
FIN          Ari Huusela           Stark                                     26.390041
FRA          Fabrice Amedeo        Newrest - Art Et Fenetres                 26.965000
FRFRA        Sébastien Destremau   Merci                                     27.872902
Name: Rank, dtype: float64


df_all_rankings.index.max()-df_all_rankings.index.min()

Timedelta('79 days 23:05:00')


# we prepare our plot
fig = plt.figure(figsize=(20, 8));
k = 1

# we pick 4 rndom skippers
skippers = np.random.choice(df_all_rankings['Skipper'].unique(), 4)
columns_graph = ['Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h']

# for each column
for col in columns_graph:
    # we add a subplot
    ax = fig.add_subplot(1, 3, k)
    # we plot the categories in each subplot
    for sk in skippers:
        ax.plot(df_all_rankings[df_all_rankings['Skipper'] == sk].index,
                df_all_rankings.loc[df_all_rankings['Skipper'] == sk, col],
                label = sk, alpha=0.7);
        ax.set_title(col);
    ax.legend();
    k+=1
fig.tight_layout()


crew_first_last = df_all_rankings.loc[(df_all_rankings['Rank'] == 1) & (df_all_rankings.index == df_all_rankings[df_all_rankings['Rank'] == 1].index.max()), 'Crew'].values[0]
crew_first_last

'Apivia'


# we get the average 24h VMG and Distance for the crew we just isolated
VMG_24_APIVIA = df_all_rankings.loc[df_all_rankings['Crew'] == crew_first_last, ('VMG kts 24h', 'Rank')]

# we graph its regression using Seaborn
sns.set(rc={'figure.figsize':(11.7,8.27)})
sns.regplot(x='VMG kts 24h', y='Rank', data=VMG_24_APIVIA,  marker="+");


# we define our variables x and y_true for our regression
# we are removing lines with negative VMG
x = df_all_rankings.loc[df_all_rankings['VMG kts 24h']>0, ['VMG kts 24h']].values.reshape(-1, 1)
y_true = df_all_rankings.loc[df_all_rankings['VMG kts 24h']>0, ['Rank']].values

# we split the dataset to create a train and a test dataset
x_train, x_test, y_train, y_test = train_test_split(x, y_true, test_size=0.25)

# we perform the linear regression using sklearn
linmod = linear_model.LinearRegression()
linmod.fit(x_train, y_train)

y_pred = linmod.predict(x_test)

# we plot the graph
fig = plt.figure(figsize=(12, 7));
ax = fig.add_subplot()
ax.scatter(x=x_test, y=y_test, alpha=0.7, label='true values')
ax.plot(x_test, y_pred, color='orange', alpha=0.7, label='predicted values')

ax.set_xlabel('VMG kts 24h');
ax.set_ylabel('Rank');
ax.set_title('Rank vs VMG kts 24h');
ax.legend();


# we calculate the prediction error
risk_square_OLS= mean_squared_error(y_test, y_pred)

# we calculate the R2 score
R_square_OLS = r2_score(y_test, y_pred)

print('Le coefficient de détermination est de R²={:.2f}'.format(R_square_OLS))
print('Le risque de prediction est de Rpred={:.2f}'.format(risk_square_OLS))

Le coefficient de détermination est de R²=0.12
Le risque de prediction est de Rpred=63.45


r = y_test - y_pred
# modify the size of the seaborn plot
sns.set(rc={'figure.figsize':(12,7)})

# we plot the residus
sns.kdeplot(r.reshape(-1,), label='Residus régression linéaire');

# we add a histogram
sns.histplot(r.reshape(-1,), stat='density', alpha=0.5);
plt.xlabel('i')
plt.legend();


# tracé pairplot avec Seaborn
sns.pairplot(df_all_rankings[['Rank', 'Heading ° 24h', 'Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF']].reset_index(),
             height=3,
             kind='reg', 
             plot_kws={'line_kws':{'color':'orange', 'alpha':0.5}, 'scatter_kws': {'alpha': 0.7}});


# we define our variables x and y_true for our regression
# we are removing lines with negative columns
cols_regression = ['Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF']
x = df_all_rankings.loc[(df_all_rankings[cols_regression] > 0).all(axis=1), cols_regression]
y_true = df_all_rankings.loc[(df_all_rankings[cols_regression] > 0).all(axis=1), ['Rank']].values

# we split the dataset to create a train and a test dataset
x_train, x_test, y_train, y_test = train_test_split(x, y_true, test_size=0.25)

# we perform the linear regression using sklearn
linmod = linear_model.LinearRegression()
linmod.fit(x_train, y_train)

y_pred = linmod.predict(x_test)

# we calculate the prediction error
risk_square_OLS_multivar = mean_squared_error(y_test, y_pred)

# we calculate the R2 score
R_square_OLS_multivar = r2_score(y_test, y_pred)

print('Le coefficient de détermination est de R²={:.2f}'.format(R_square_OLS_multivar))
print('Le risque de prediction est de Rpred={:.2f}'.format(risk_square_OLS_multivar))

Le coefficient de détermination est de R²=0.19
Le risque de prediction est de Rpred=56.13


# we request the url
headers = {'User-Agent' : 'Mozilla/5.0'}
req_skip = requests.get('https://www.vendeeglobe.org/fr/glossaire', headers = headers)

# we raise an Exception if the status code is not 200
if req_skip.status_code != 200:
    raise Exception('Couldn\'t get open URL')

# we get our soup from the downloaded content
soup_skip = BeautifulSoup(req_skip.content)

# we extract the arrival dates from the website
df_list = []
for div in soup_skip.findAll('div', attrs={'class': 'boats-list__popup-infos'}):
    # we get the skipper name
    skip_name = div.find('h3', attrs={'class': 'boats-list__popup-title'}).text.title()
    skippers_info = {'Equipage':skip_name}
    # we get the rest of the data
    for li in div.find('ul', attrs={'class': 'boats-list__popup-specs-list'}).findAll('li'):
        info = li.text.split(' : ')
        skippers_info[info[0].strip()] = [info[1].strip()]

    # we append all data as dataframes
    df_list.append(pd.DataFrame(skippers_info))

# we generate a dataframe containing all of the extracted data
df_skippers = pd.concat(df_list, ignore_index=True)

df_skippers.head()

# we replace the former names of the boat by a propoer list of strings
df_skippers['Anciens noms du bateau'] = df_skippers['Anciens noms du bateau'].fillna('').str.strip().str.split(',')

# we convert all numerical value into floats or int
numerical_cols = ['Longueur', 'Largeur', 'Tirant d\'eau', 'Déplacement (poids)','Hauteur mât','Surface de voiles au près','Surface de voiles au portant']
df_skippers[numerical_cols] = df_skippers[numerical_cols].apply(lambda x: x.str.replace(re.compile('m2|m²|m|t[a-z]*|nc|NC'), '').str.replace(',', '.')).apply(pd.to_numeric)

# we rename the columns to include the units
df_skippers = df_skippers.rename(columns={'Longueur': 'Longueur (m)', 'Largeur': 'Largeur (m)', 'Tirant d\'eau': 'Tirant d\'eau (m)', 'Déplacement (poids)': 'Déplacement (poids) (t)','Hauteur mât': 'Hauteur mât (m)','Surface de voiles au près': 'Surface de voiles au près (m2)', 'Surface de voiles au portant': 'Surface de voiles au portant (m2)'})

# we correct the 'foiler' vs 'foils' categories
df_skippers['Nombre de dérives'] = df_skippers['Nombre de dérives'].str.replace('foiler', 'foils')

# we convert all dates into datetimes
locale.setlocale(locale.LC_ALL, 'fr')
df_skippers['Date de lancement'] = df_skippers['Date de lancement'].apply(lambda x: datetime.datetime.strptime(x, '%d %B %Y'))

df_skippers.head()


# export to pickle
df_skippers.to_pickle('df_skippers.pkl')


# import back in
df_skippers = pd.read_pickle('df_skippers.pkl')


# we merge the two dataframes
df_ranking_skippers = pd.merge(left=df_all_rankings.reset_index(), right=df_skippers, left_on='Crew', right_on='Equipage', how='inner').set_index('Hour FR')
df_ranking_skippers.sample(5)


# we agregate some numerical values by Type of foil
df_ranking_skippers.groupby(['Nombre de dérives']).agg({'Crew': lambda x: x.nunique(),
                                                        'Speed kts 24h': np.mean,
                                                        'VMG kts 24h': np.mean,
                                                        'Distance nm 24h': np.mean})


sns.barplot(data=df_ranking_skippers, y='Crew', x='Distance nm 24h', hue='Nombre de dérives', orient='h');


# we prepare our plot
fig = plt.figure(figsize=(20, 8));
k = 1

# we extract the 4 different types of foils
types = df_ranking_skippers['Nombre de dérives'].unique()
columns_graph = ['Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h']

# for each column
for col in columns_graph:
    # we add a subplot
    ax = fig.add_subplot(1, 3, k)
    # we plot the categories in each subplot
    for t in types:
        ax.plot(df_ranking_skippers[df_ranking_skippers['Nombre de dérives'] == t].resample('D').mean().index,
                df_ranking_skippers.loc[df_ranking_skippers['Nombre de dérives'] == t, col].resample('D').mean(),
                label = t, alpha=0.7);
        ax.set_title(col);
    ax.legend();
    k+=1
fig.tight_layout()


sns.heatmap(pd.crosstab(df_ranking_skippers['Rank'], df_ranking_skippers['Nombre de dérives']), cmap='Blues', annot=True, fmt='d');


# we prepare our plot
fig = plt.figure(figsize=(20, 8));
k = 1

# we pick 4 random crews
crew = np.random.choice(df_ranking_skippers['Crew'].unique(), 4, replace=False)
columns_graph = ['Distance nm 30min', 'Distance nm 24h', 'Distance nm last report']

# for each column
for col in columns_graph:
    # we add a subplot
    ax = fig.add_subplot(1, 3, k)
    # we plot the categories in each subplot
    for cw in crew:
        ax.plot(df_ranking_skippers[df_ranking_skippers['Crew'] == cw].resample('W-MON').mean().index,
                df_ranking_skippers.loc[df_ranking_skippers['Crew'] == cw, col].resample('W-MON').mean(),
                label = cw, alpha=0.7);
        ax.set_title(col);
    ax.legend();
    k+=1
fig.tight_layout()


# we prepare our plot
fig = plt.figure(figsize=(20, 8));
k = 1

# we pick 4 random crews
crew = df_all_rankings['Crew'].unique()
columns_graph = ['Distance nm 30min', 'Distance nm 24h', 'Distance nm last report']

# we create an aggregate, that will be useful tp categorize data later
agg = df_ranking_skippers.pivot_table(values='Nombre de dérives', index='Crew', aggfunc='first')
#df_ranking_skippers.groupby('Crew')['Nombre de dérives'].last()

# we generate custom legends
custom_legend = [Patch(color='purple', label='foils'),
                Patch(color='royalblue', label='others/unknown')]

# for each column
for col in columns_graph:
    # we add a subplot
    ax = fig.add_subplot(1, 3, k)
    # we plot the categories in each subplot
    for cw in crew:
        ax.barh(y=cw, width=df_all_rankings.loc[df_all_rankings['Crew'] == cw, col].mean(),
                color = 'purple' if cw in agg.index and (agg.loc[agg.index==cw, 'Nombre de dérives'] == 'foils').all() else 'royalblue');
        ax.set_title(col);
        
        # we hide the labels for the other 2 plots
        if k > 1:
            ax.get_yaxis().set_visible(False)
    k+=1
    # we generate and attach the custom legend
    ax.legend(handles=custom_legend)
fig.tight_layout()


crews = np.random.choice(df_all_rankings['Crew'].unique(), 4)
colors = ['cyan', 'magenta', 'yellow', 'black']
m = Map(center = (0, 0), zoom=2)
legend = LegendControl({cw:cl for cw,cl in zip(crews, colors)}, name="Legend", position="topleft")

m.add_control(legend)

for i, cw in enumerate(crews):
    latitudes = df_all_rankings.loc[df_all_rankings['Crew']==cw, 'Latitude'].values
    longitudes = df_all_rankings.loc[df_all_rankings['Crew']==cw, 'Longitude'].values

    line = Polyline(
        locations=list(zip(latitudes, longitudes)),
        color=colors[i] ,
        opacity=0.5,
        weight=2,
        fill=False
    )
    m.add_layer(line)

m


fig = px.line_geo(df_all_rankings, lat='Latitude', lon='Longitude',
                  color="Crew",
                  projection="orthographic")
fig.show()


df_vmg_average = df_all_rankings.pivot_table(values='VMG kts 24h', index='Hour FR', aggfunc='mean').resample('4H').mean().fillna(method='ffill')
df_vmg_average.head()


df_vmg_average['Rolling 5d'] = df_vmg_average['VMG kts 24h'].rolling('5d').mean()

fig = plt.figure(figsize=(20, 8));
ax = fig.add_subplot(111)
obj = ax.plot(df_vmg_average)
ax.set_title('VMG kts 24h');
plt.legend(iter(obj), ('VMG kts 24h', 'Rolling 5d'));
ax.autoscale(axis='x',tight=True);


# exp smoothing 5d = 4h * (6h * 5d)
df_vmg_average['Exp Smoothing 5d']=SimpleExpSmoothing(df_vmg_average['VMG kts 24h']) \
                                .fit(smoothing_level=2/(30+1),optimized=False).fittedvalues.shift(-1)

# we plot the results
fig = plt.figure(figsize=(20, 8));
ax = fig.add_subplot(111)
obj = ax.plot(df_vmg_average)
ax.set_title('VMG kts 24h');
plt.legend(iter(obj), ('VMG kts 24h', 'Rolling 5d', 'Exp Smoothing 5d'));
ax.autoscale(axis='x',tight=True);


# decomposition with statsmodels
decompo = seasonal_decompose(df_vmg_average['VMG kts 24h'], model='add', period=6)
decompo.plot();


# creation of the train and test data sets
df_train = df_vmg_average.iloc[:-100]
df_test = df_vmg_average.iloc[-100:]


# create and fit the SARIMAX model
sairmax_mod = SARIMAX(df_train['VMG kts 24h'],order=(0,1,1),seasonal_order=(2,0,0,48))
results = sairmax_mod.fit()
results.summary()


predictions = results.predict(start=df_test.index[0], end=df_test.index[-1], dynamic=False, typ='levels').rename('SARIMA(0,1,1)(2,0,0,12) Predictions')

C:\ProgramData\Anaconda3\lib\site-packages\statsmodels\tsa\base\tsa_model.py:132: FutureWarning:

The 'freq' argument in Timestamp is deprecated and will be removed in a future version.


fig = plt.figure(figsize=(20, 8));
ax = fig.add_subplot(111)
ax.plot(df_test['VMG kts 24h'], label='true values')
ax.set_title('VMG kts 24h');
predictions.plot(legend=True)
ax.autoscale(axis='x',tight=True)
ax.legend();


numerical_cols = ['Longueur (m)', 'Largeur (m)', 'Tirant d\'eau (m)', 'Déplacement (poids) (t)', 'Hauteur mât (m)', 'Surface de voiles au près (m2)', 'Surface de voiles au portant (m2)']
df_skippers_classif = df_skippers[[*numerical_cols, 'Voile quille']].copy()

# convert 'Voile quille' into categorical
df_skippers_classif['Voile quille'] = pd.Categorical(df_skippers_classif['Voile quille']).codes #.astype('category')

# convert 'Nombre de dérives' into one hot encoding
df_skippers_classif = df_skippers_classif.join(pd.get_dummies(df_skippers['Nombre de dérives'], prefix='dérive'))

# we fill the na values with the average on the column
df_skippers_classif = df_skippers_classif.apply(lambda x: x.fillna(x.mean()),axis=0)

df_skippers_classif.sample(5)


# normalisation avec sklearn :
normalizer = preprocessing.StandardScaler().fit(df_skippers_classif[[*numerical_cols, 'Voile quille']])
df_skippers_classif[[*numerical_cols, 'Voile quille']] = normalizer.transform(df_skippers_classif[[*numerical_cols, 'Voile quille']])

df_skippers_classif.sample(5)


# there is a current issue with KMean classifier the following lines desable the warning
import warnings
warnings.simplefilter('ignore')

# we define a range for k
min_k = 1
max_k = 10
elbow_scores = []

# we loop on the different values of k
for k in range(min_k, max_k):
    # We run k means on each value of k
    kmeans_algo = cluster.KMeans(n_clusters=k)
    kmeans_results = kmeans_algo.fit_transform(df_skippers_classif)
    # We copy dataframe into a new dataframe for results
    df_skippers_classif_results = df_skippers_classif.copy()
    df_skippers_classif_results['labels'] = kmeans_algo.labels_
    
    # We calculate the distortion for each value of k and store it in a list
    elbow_scores.append(kmeans_algo.inertia_)

# We plot the distortions for each value of k
plt.figure(figsize=(12, 7));
plt.plot(list(range(min_k, max_k)), elbow_scores, label='elbow_scores')
plt.xlabel("k");
plt.ylabel("Distortion");
plt.title('Distortion vs value of k');


kmeans_algo = cluster.KMeans(n_clusters=5)
kmeans_results = kmeans_algo.fit_transform(df_skippers_classif)
df_skippers_classif_results['labels'] = kmeans_algo.labels_

# affichage des résultats
df_skippers_classif_recap = pd.merge(left=df_skippers_classif_results, right=df_skippers,left_index=True, right_index=True)[['Equipage', 'labels']].sort_values('labels')
df_skippers_classif_recap.pivot_table(values='Equipage', index='labels', aggfunc='count')


# pie plot
df_skippers_classif_recap.pivot_table(values='Equipage', index='labels', aggfunc='count') \
.plot(kind='pie', y='Equipage', autopct='%1.2f%%', textprops={'weight': 'bold'});
plt.legend();

	Rank	Nationality	Sail	Skipper	Crew	Latitude	Longitude	Heading ° 30min	Speed kts 30min	VMG kts 30min	...	Heading ° last report	Speed kts last report	VMG kts last report	Distance nm last report	Heading ° 24h	Speed kts 24h	VMG kts 24h	Distance nm 24h	DTF	DTL
Hour FR
2020-11-08 17:00:00	1	FRA	8	Jérémie Beyou	Charal	46.353333	-2.597222	268	26.0	22.6	...	262	21.1	19.7	31.7	254	1.4	1.4	34.5	24265.1	0.0
2020-11-08 17:00:00	2	FRA	79	Charlie Dalin	Apivia	46.296111	-2.529167	262	24.8	22.8	...	357	0.0	0.0	2782.5	247	1.4	1.4	32.9	24265.8	0.7
2020-11-08 17:00:00	3	GBR	99	Alex Thomson	Hugo Boss	46.318889	-2.535000	263	22.8	20.8	...	257	19.9	19.1	29.9	249	1.4	1.3	32.7	24266.3	1.2
2020-11-08 17:00:00	4	FRA	59	Thomas Ruyant	Linkedout	46.329167	-2.541389	266	24.3	21.6	...	260	19.9	18.9	29.9	251	1.4	1.3	32.7	24266.3	1.2
2020-11-08 17:00:00	5	MON	10	Boris Herrmann	Seaexplorer - Yacht Club De Monaco	46.306111	-2.500833	263	23.9	21.9	...	257	19.0	18.2	28.2	248	1.3	1.3	31.5	24267.3	2.2

	Equipage	Numéro de voile	Anciens noms du bateau	Architecte	Chantier	Date de lancement	Longueur (m)	Largeur (m)	Tirant d'eau (m)	Déplacement (poids) (t)	Nombre de dérives	Hauteur mât (m)	Voile quille	Surface de voiles au près (m2)	Surface de voiles au portant (m2)
0	Newrest - Art & Fenêtres	FRA 56	[No Way Back, Vento di Sardegna]	VPLP/Verdier	Persico Marine	2015-08-01	18.28	5.85	4.5	7.0	foils	29.0	monotype	320	570
1	Pure - Best Western®	FRA 49	[Gitana Eighty, Synerciel, Newrest-Matmut]	Bruce Farr Design	Southern Ocean Marine (Nouvelle Zélande)	2007-03-08	18.28	5.80	4.5	9.0	2	28.0	acier forgé	280	560
2	Tse - 4Myplanet	FRA72	[Famille Mary-Etamine du Lys, Initiatives Coe...	Marc Lombard	MAG France	1998-03-01	18.28	5.54	4.5	9.0	2	29.0	acier	260	580
3	Maître Coq Iv	17	[Safran 2 - Des Voiles et Vous]	Verdier - VPLP	CDK Technologies	2015-03-12	18.28	5.80	4.5	8.0	foils	29.0	acier mécano soudé	310	550
4	Charal	08	[]	VPLP	CDK Technologies	2018-08-18	18.28	5.85	4.5	8.0	foils	29.0	acier	320	600

	Rank	Nationality	Sail	Skipper	Crew	Latitude	Longitude	Heading ° 30min	Speed kts 30min	VMG kts 30min	...	Date de lancement	Longueur (m)	Largeur (m)	Tirant d'eau (m)	Déplacement (poids) (t)	Nombre de dérives	Hauteur mât (m)	Voile quille	Surface de voiles au près (m2)	Surface de voiles au portant (m2)
Hour FR
2020-11-21 04:30:00	22	FRA	14	Arnaud Boissieres	La Mie Câline - Artisans Artipôle	7.125000	-28.608889	219	8.8	5.8	...	2007-08-03	18.28	5.65	4.5	7.9	foils	29.0	basculante avec vérin	300	610
2020-12-26 17:30:00	20	ESP	33	Didac Costa	One Planet One Ocean	-46.571944	143.775556	119	15.5	15.4	...	2000-02-02	18.28	5.30	4.5	8.9	2	26.0	acier	240	470
2021-01-24 08:30:00	19	JPN	11	Kojiro Shiraishi	Dmg Mori Global One	-20.474444	-31.627222	8	12.8	12.7	...	2019-09-05	18.28	5.85	4.5	8.0	foils	29.0	acier forgé	320	580
2020-12-01 08:30:00	17	SUI	7	Alan Roura	La Fabrique	-39.869444	-10.003056	114	10.8	10.8	...	2007-08-01	18.28	5.90	4.5	NaN	foils	27.0	carbone	290	580
2021-01-25 17:30:00	25	FIN	222	Ari Huusela	Stark	-55.100278	-63.904167	57	12.3	11.7	...	2007-08-06	18.28	5.80	4.5	8.5	2	28.0	acier	270	580

	Crew	Speed kts 24h	VMG kts 24h	Distance nm 24h
Nombre de dérives
2	7	11.406769	10.446086	273.599728
2 asymétriques	1	11.388589	10.174689	273.325934
foils	15	12.936539	11.762465	310.441616

	VMG kts 24h
Hour FR
2020-11-08 12:00:00	0.186111
2020-11-08 16:00:00	2.029688
2020-11-08 20:00:00	4.818182
2020-11-09 00:00:00	4.818182
2020-11-09 04:00:00	6.709091

Projet Kit Big Data MDI 721¶

Importation des librairies¶

Récupération des fichiers Excel avec les classements¶

Première exploration des données¶

Corrélation et régression linéaire¶

Extraction des données sur les voiliers¶

Rapprochement des données des voiliers avec celle des classements¶

Impact de la présence d'un foil¶

Visualisation de la distance parcourue par voilier¶

Cartes avec les routes d'un ou plusieurs voiliers¶

Analyses de séries temporelles¶

Application d'algorithmes statistiques¶

	Rank	Nationality	Sail	Skipper	Crew	Latitude	Longitude	Heading ° 30min	Speed kts 30min	VMG kts 30min	...	Heading ° last report	Speed kts last report	VMG kts last report	Distance nm last report	Heading ° 24h	Speed kts 24h	VMG kts 24h	Distance nm 24h	DTF	DTL
Hour FR
2020-11-08 15:30:00	1	FRA	18	Louis Burton	Bureau Vallée 2	46.412778	-1.846667	241	17.7	17.5	...	357	0.0	0.0	2788.0	201	0.3	0.3	6.1	24293.9	0.0
2020-11-08 15:31:00	2	MON	10	Boris Herrmann	Seaexplorer - Yacht Club De Monaco	46.409444	-1.839444	241	11.1	10.9	...	357	0.0	0.0	2787.9	196	0.3	0.2	6.0	24294.2	0.4
2020-11-08 15:30:00	3	FRA	8	Jérémie Beyou	Charal	46.425278	-1.844167	244	15.5	15.5	...	357	0.0	0.0	2788.5	199	0.2	0.2	5.5	24294.3	0.5
2020-11-08 15:30:00	4	FRA	59	Thomas Ruyant	Linkedout	46.419722	-1.835556	244	13.2	13.1	...	357	0.0	0.0	2788.3	196	0.2	0.2	5.6	24294.5	0.6
2020-11-08 15:30:00	5	FRA	53	Maxime Sorel	V And B Mayenne	46.416389	-1.832222	246	10.9	10.9	...	357	0.0	0.0	2788.1	195	0.8	0.7	5.8	24294.5	0.6

Dep. Variable:	VMG kts 24h	No. Observations:	381
Model:	SARIMAX(0, 1, 1)x(2, 0, [], 48)	Log Likelihood	-457.357
Date:	ven., 05 nov. 2021	AIC	922.714
Time:	18:44:05	BIC	938.474
Sample:	11-08-2020	HQIC	928.967
	- 01-10-2021
Covariance Type:	opg

	coef	std err	z	P>\|z\|	[0.025	0.975]
ma.L1	-0.2584	0.030	-8.687	0.000	-0.317	-0.200
ar.S.L48	-0.0649	0.047	-1.385	0.166	-0.157	0.027
ar.S.L96	-0.1070	0.060	-1.773	0.076	-0.225	0.011
sigma2	0.6477	0.023	28.504	0.000	0.603	0.692

Ljung-Box (L1) (Q):	0.08	Jarque-Bera (JB):	991.83
Prob(Q):	0.78	Prob(JB):	0.00
Heteroskedasticity (H):	0.25	Skew:	0.73
Prob(H) (two-sided):	0.00	Kurtosis:	10.78

	Largeur (m)	Déplacement (poids) (t)	Hauteur mât (m)	Surface de voiles au près (m2)	Surface de voiles au portant (m2)	Voile quille	dérive_2	dérive_foils
18	0.587512	7.581751e-01	-0.491914	-1.032094	-0.165597	-0.599878	1	0
15	0.261116	7.581751e-01	0.737872	-0.035242	0.675125	-0.128545	1	0
24	1.240303	3.727721e-15	-1.721701	-0.367526	-0.165597	1.756784	0	1
4	0.913908	-2.910851e-01	0.737872	0.629326	0.254764	-0.599878	0	1
0	0.913908	-2.389606e+00	0.737872	0.629326	-0.375777	2.228117	0	1